package crm.control;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import java.sql.ResultSet;
import crm.dbcoon.DBConnect;
import crm.model.Offer;
import crm.util.Pagination;



/*
 * 报价相关方法
 * @author  Cyoung
 * @date 2012 07 20
 */
public class OfferControl {
	public static Pagination pageInstance = null;
	
	
	/**
	 * 转入报价，在转入报价的同时，需先修改对应 的recepit状态
	 * 每个报价（offer）对应一个跟单（recepit）
	 * @param of
	 * @return
	 */
	public static boolean addOffer(Offer of,int stage)  {
		DBConnect dbc = new DBConnect();
		boolean flag = false;
		String sql =  "insert into control_offer(recepit_id,state,offer_money,theme,department,date,manager) values(?,?,?,?,?,?,?)";
	    dbc.prepareStatement(sql);
	    System.out.println(sql);
	    try{
	    	   //setRs(dbConnect, of);
	    	int index =1;
			dbc.setInt(index++, of.getRecepit_id());
			dbc.setString(index++, of.getState());
			dbc.setString(index++, of.getOffer_money());
			dbc.setString(index++, of.getTheme());
			dbc.setString(index++, of.getDepartment());
			dbc.setString(index++, of.getDate());
			dbc.setString(index++, of.getManager());
	    	    //修改对应跟单状态为3，对应param_recepitstage
	    	   if(dbc.executeUpdate()&&RecepitControl.updateStage(of.getRecepit_id(),stage)){
	    	      flag = true;
	    	}
	    }catch (SQLException e) {
			// TODO: handle exception
	    	e.printStackTrace();
	    	System.out.println("addOffer出错");
		}finally{
			dbc.close();
		}
		return flag;
	}
	
	public static boolean deleteOffer(int id){
		DBConnect dbConnect =new DBConnect();
		String sql = "delete from control_offer where id = '"+id+"'";
		dbConnect.prepareStatement(sql);
		try {
			dbConnect.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("deleteOffer方法出错");
			return false;
		}finally{
			dbConnect.close();
		}
	}
	
	public  static boolean updateOffer(Offer of) {
		DBConnect dbcConnect = new DBConnect();
		String sql = "update control_offer set recepit_id=?,state=?,ofer_money=?,department=?,date=?,manager=? where id= '"+of.getId()+"'";
		dbcConnect.prepareStatement(sql);
		try {
			setRs(dbcConnect, of);
			dbcConnect.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("updateOffer出错");
			return false;
		}finally{
			dbcConnect.close();
		}
		
	}
	
	public static boolean updateState(int offerId,int state){
		DBConnect dbConnect = new DBConnect();
		int recepitId = OfferControl.getOfferById(offerId).getRecepit_id();
		int oldStage =RecepitControl.getRecepitById(recepitId).getStage();
		if(state==1)
			oldStage+=1; //报价通过就在recepit数据里的stage进入写一个阶段
		String sql = "update control_offer set state = '"+state+"' where id='"+offerId+"'";
		dbConnect.prepareStatement(sql);
		try {
			if (dbConnect.executeUpdate()&&RecepitControl.updateStage(recepitId, oldStage)) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	public  static Offer getOfferById(int id) {
		DBConnect dbConnect = new DBConnect();
		ResultSet rs = null;
		Offer offer = new Offer();
		String sql ="select * from control_offer where id = '"+id+"' ";
		dbConnect.prepareStatement(sql);
		try {
			rs = (ResultSet) dbConnect.executeQuery();
			if(rs.next()){
				getRs(rs, offer);
			}
			return offer;
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getOfferById出错");
			return null;
		}finally{
			dbConnect.close();
		}
	}
	
	public static void setRs(DBConnect dbc,Offer of){
		try {
			int index =1;
			dbc.setInt(index++, of.getRecepit_id());
			dbc.setString(index++, of.getState());
			dbc.setString(index++, of.getOffer_money());
			dbc.setString(index++, of.getTheme());
			dbc.setString(index++, of.getDepartment());
			dbc.setString(index++, of.getDate());
			dbc.setString(index++, of.getManager());
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("offerControl中setRs出错");
		}
	}
	public static void getRs(ResultSet rs,Offer offer){
		try {
			offer.setDate(rs.getString("date"));
			offer.setDepartment(rs.getString("department"));
			offer.setId(rs.getInt("id"));
			offer.setManager(rs.getString("manager"));
			offer.setOffer_money(rs.getString("offer_money"));
			offer.setRecepit_id(rs.getInt("recepit_id"));
			offer.setState(rs.getString("state"));
			offer.setTheme(rs.getString("theme"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("OfferControl.getRs方法出错");
		}
	}
	
	/**
	 * 将报价转为合同
	 * @param theme
	 * @return
	
	public static boolean turnToBargain(int offerId){
		DBConnect dbc = new DBConnect();
		Offer offer = OfferControl.getOfferById(offerId);
		Bargain bargain = new Bargain();
	} */
	
	/**
	 * 搜索：管理员能搜索所有，普通员工能搜索自己参与过的、创建的跟单转过来的合同，以及自己谈成的合同
	 * @param theme
	 * @param state
	 * @param manager
	 * @param customer
	 * @param date
	 * @param uid
	 * @param pages
	 * @param rowPerPage
	 * @return
	 */
	public static Collection getAllOffers(String theme,String state,String manager,String customer,String date,String uid,int pages,int rowPerPage ) {
		String myname = "%";
		if(theme==null||theme.equals("")){
			theme = "%";
		}
		if(state==null||state.equals("")){
			state = "%";  
		}
		if(manager==null||manager.equals("")){
			manager = "%";
		}
		if(customer==null||customer.equals("")){
			customer=  "%";
		}
		if(date==null||date.equals("")){
			date = "%";
		}
		if(uid!=null&&!uid.equals("")){
			myname = EmployeesControl.getEmpolyeesById(Integer.parseInt(uid)).getName();
			uid="@"+uid+"@%";
		}
		if(uid==null||uid.equals("")){
			uid="%";
		}
		
		String sql = "select * from control_offer,control_recepit,info_customer where control_offer.recepit_id = control_recepit.id and control_offer.theme like '%"+theme+"%'" +
				" and control_offer.state like '"+state+"' and control_offer.manager like '%"+manager+"%' and control_recepit.consumer_id = info_customer.id and info_customer.name " +
						" like '%"+customer+"%' and control_offer.date like '%"+date+"%' and (control_recepit.person1 like '"+uid+"' or  control_recepit.person2 like '"+uid+"' " +
								"or control_recepit.person3 like '"+uid+"' or control_recepit.creator like '%"+myname+"%' or control_offer.manager like '%"+myname+"%') order by control_offer.id desc";
		pageInstance = new OfferControlPage(sql, rowPerPage);
		try {
			return pageInstance.getPage(pages);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("OfferControl获取分页异常");
			return null;
		}
		
	}
}
class OfferControlPage extends Pagination{
	public OfferControlPage(String sql ,int rowsPerPage) {
		super(sql, rowsPerPage, 1);
	}
	
	protected Collection<Offer> packResultSet(ResultSet rs) {
		Collection<Offer> offers = new ArrayList<Offer>();
		try {
			while(rs.next()){
				Offer offer = new Offer();
				OfferControl.getRs(rs, offer);
				offers.add(offer);
			}
			return offers;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("offerControl分页显示Collection出错");
			return null;
		}
	}

}

